﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using DTO;

namespace DAO
{

	public class ProductsDAO:IData
 	{
		public ProductsDAO(String myconnecString) : base(myconnecString) { }

		public  int OnSubmit(Products item, int t) {
            return connec.ExecuteNonQuery("sp_Products", CommandType.StoredProcedure, new SqlParameter("@flag", t),
                new SqlParameter("@" + Products.c_id, item.id), new SqlParameter("@" + Products.c_groupProductId, item.groupProductId),
                new SqlParameter("@" + Products.c_productName, item.productName), new SqlParameter("@" + Products.c_description, item.description),
                new SqlParameter("@" + Products.c_content, item.content), new SqlParameter("@" + Products.c_create_on, item.create_on),
                new SqlParameter("@" + Products.c_price, item.price), new SqlParameter("@" + Products.c_vendorId, item.vendorId),
                new SqlParameter("@" + Products.c_updatePriceLast, item.updatePriceLast), new SqlParameter("@" + Products.c_pathImage, item.pathImage),
                new SqlParameter("@" + Products.c_isActive, item.isActive)); 
		}

		public  Products GetItem(IDataReader dr)
		{
            if (dr == null)
                return null;
			Products obj = new Products();
			obj.id = Convert.ToInt64(dr[Products.c_id]);
			obj.groupProductId = Convert.ToInt64(dr[Products.c_groupProductId]);
			obj.productName = Convert.ToString(dr[Products.c_productName]);
			obj.description = Convert.ToString(dr[Products.c_description]);
			obj.content = Convert.ToString(dr[Products.c_content]);
			obj.create_on = Convert.ToDateTime(dr[Products.c_create_on]);
			obj.price = Convert.ToDecimal(dr[Products.c_price]);
			obj.vendorId = Convert.ToInt32(dr[Products.c_vendorId]);
			obj.updatePriceLast = Convert.ToDecimal(dr[Products.c_updatePriceLast]);
			obj.pathImage = Convert.ToString(dr[Products.c_pathImage]);
			obj.isActive = Convert.ToInt32(dr[Products.c_isActive]);
            obj.amount = Convert.ToInt32(dr[Products.c_amount]);
			return obj;
		}

		public  List<Products> GetAll()
		{
            return GetAll("select top 27 * from " + Products.b_Products + " order by id desc ", CommandType.Text);
		}

		public  Products GetItemById(object id)
		{
			 return this.GetAll().FirstOrDefault(x => x.id == (long)id);
            //return this.GetItem(this.getReader("sp_getItemProduct", "id", id));
		}

        

        private List<Products> GetAll(String strSQL, CommandType commandtype)
        {
            List<Products> list = new List<Products>();
            SqlConnection connection = connec.Connection;
            SqlCommand command = new SqlCommand();
            SqlParameter p = new SqlParameter();
            p.Direction = ParameterDirection.Output;
            command.CommandType = commandtype;
            command.CommandText = strSQL;
            command.Connection = connection;
            connection.Open();
            SqlDataReader reader;
            using (reader = command.ExecuteReader(CommandBehavior.CloseConnection))
            {
                while (reader.Read())
                {
                    list.Insert(0, GetItem(reader));
                }
                reader.Close();
            }
            connection.Close();
            return list;
        }

        public List<Products> findProducts(String find,int flag)
        {
            List<Products> list = new List<Products>();
            SqlConnection connection = connec.Connection;
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "sp_findProducts";
            SqlParameter param = new SqlParameter("@find", SqlDbType.NVarChar);
            param.Value = find;
            command.Parameters.Add(param);
            param = new SqlParameter("@flag", SqlDbType.Int);
            param.Value = flag;
            command.Parameters.Add(param);
            command.Connection = connection;
            connection.Open();
            SqlDataReader reader;
            using (reader = command.ExecuteReader(CommandBehavior.CloseConnection))
            {
                while (reader.Read())
                {
                    list.Add(GetItem(reader));
                }
                reader.Close();
            }
            connection.Close();
            return list;
        }

        public List<Products> GetAllByGroupId(long groupId) {
            List<Products> list = new List<Products>();
            SqlConnection connection = connec.Connection;
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "sp_GetProductsBygroupId";
            SqlParameter param = new SqlParameter("@groupId", SqlDbType.BigInt);
            param.Value = groupId;
            command.Parameters.Add(param);
            command.Connection = connection;
            connection.Open();
            SqlDataReader reader;
            using (reader = command.ExecuteReader(CommandBehavior.CloseConnection))
            {
                while (reader.Read())
                {
                    list.Add(GetItem(reader));
                }
                reader.Close();
            }
            connection.Close();
            return list;            
        }
	}

 }
